﻿CREATE PROCEDURE [dbo].[Report_SpaceBaseForFloorplan]
	@RangeReviewId int
AS

declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @endDatePY DATE = DATEADD(Year, -1, @endDate)
declare @totalStores INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
declare @totalCoverage INT = @totalStores * @totalMonths

SELECT 
  pla.Name PlanogramName
, pl.Name PlanogramLocation
, pla.Level1
, pla.Level2
, sp.Level4
, ar.Code ArticleCode
, ar.UOM
, ar.Name ArticleName
, ar.Width 
, sp.Facings PlanogramFacings
, ar.Facings TotalFacings
, CASE WHEN co.IsVATIncluded = 0 THEN ISNULL(SUM(sales.Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(sales.Sales / (1 + ar.TaxRate) * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS Sales	
, CASE WHEN am.Margin is not null THEN ISNULL(SUM(sales.Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * am.Margin 
	   WHEN scm.Margin is not null THEN ISNULL(SUM(sales.Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * scm.Margin 
	   WHEN cm.Margin is not null THEN ISNULL(SUM(sales.Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * cm.Margin 
	   ELSE ISNULL(SUM(GM * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS GM	
, ISNULL(SUM(inv.Cost * ex.Rate) / CAST(@totalCoverage AS MONEY),0) as InventoryCost
, setup.LinearMeters
, setup.SquareMeters
FROM Articles ar WITH (NOLOCK)
INNER JOIN Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id 
INNER JOIN Spaces sp WITH (NOLOCK) on ar.Id = sp.ArticleId
INNER JOIN Planograms pla WITH (NOLOCK) on pla.Id = sp.PlanogramId
INNER JOIN PlanogramLocations pl WITH (NOLOCK) on pl.Id = pla.PlanogramLocationId
INNER JOIN RangeReviews rr WITH (NOLOCK) on ar.RangeReviewId = rr.Id
INNER JOIN Countries co WITH (NOLOCK) on rr.CountryId = co.Id	
INNER JOIN Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
INNER JOIN Months months WITH (NOLOCK) on 
			(months.Date BETWEEN @startDatePY AND @endDatePY OR months.Date BETWEEN @startDate AND @endDate)
INNER JOIN Stores store WITH (NOLOCK) on store.CountryId = co.Id
LEFT JOIN RangeReviewSetup setup WITH (NOLOCK) on setup.RangeReviewId = rr.Id AND setup.CategoryId = ar.CategoryId
LEFT JOIN TheoreticalMargins am WITH (NOLOCK)  on am.CountryId = co.Id AND  am.ArticleId = ar.Id  
LEFT JOIN TheoreticalMargins scm WITH (NOLOCK) on scm.CountryId = co.Id AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
LEFT JOIN TheoreticalMargins cm WITH (NOLOCK)  on cm.CountryId = co.Id AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
LEFT JOIN ArticleSales sales WITH (NOLOCK) on sales.ArticleId = ar.Id 
			AND sales.Date = months.Date 
			AND sales.Confirmed = 1
			AND sales.StoreId = store.Id
		LEFT JOIN Inventories inv WITH (NOLOCK) on inv.ArticleId = ar.Id 
			AND inv.Date = months.Date 
			AND inv.StoreId = store.Id
LEFT OUTER JOIN CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = sales.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
where 
	(setup.LinearMeters = 1 OR setup.SquareMeters = 1)
	AND ar.IsDeletedPerformance = 0
	AND store.Id in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
group by 
	pla.Name
  , pl.Name
  , pla.Level1
  , pla.Level2
  , sp.Level4
  , ar.Code
  , ar.UOM
  , ar.Name  
  , ar.Width
  , sp.Width
  , ar.Facings
  , sp.Facings  
  , co.IsVATIncluded
  , am.Margin
  , cm.Margin
  , scm.Margin
  , setup.LinearMeters
  , setup.SquareMeters

